from sqlalchemy import create_engine, ForeignKey, Column, String, Integer, CHAR
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
# https://www.cnblogs.com/chenxi67/p/10376617.html

Base = declarative_base()


class Todolist(Base):
    __tablename__ = "homework"

    order = Column("order", Integer, autoincrement=True,primary_key=True)
    content = Column("content", String(255))
    condition = Column("condition", String(255), default="Done")

    def __init__(self, content):
        self.content = content


# echo=True会打印出来orm执行的sql语句
# dialect+driver://username:password@db_address/db_name
engine = create_engine("mysql+pymysql://root:6666@localhost:3306/todolist?charset=utf8")
Base.metadata.create_all(bind=engine)

Session = sessionmaker(bind=engine)
session = Session()


def fetch_all():
    total_list = []
    results = session.query(Todolist).all()
    for i in results:
        dic = {}
        print(i.order, i.content, i.condition)
        dic['order'] = i.order
        dic['content'] = i.content
        dic['condition'] = i.condition
        total_list.append(dic)
    return total_list

def get_info():
    all_num = session.query(Todolist).count()
    done = session.query(Todolist).filter(Todolist.condition == 'Done').count()
    achieve = session.query(Todolist).filter(Todolist.condition == 'Done').count()
    return all_num, done, achieve

def insert(content):
    tdl = Todolist(content)
    session.add(tdl)
    session.commit()

def delete(order):
    session.query(Todolist).filter(Todolist.order==order).delete()
    session.commit()

def edit(order, content):
    session.query(Todolist).filter(Todolist.order == order).update({"content":content})
    session.commit()

def change_condition(order, condition):
    session.query(Todolist).filter(Todolist.order == order).update({"condition":condition})
    session.commit()


